package com.maxd.respository.helpTask;

import com.maxd.model.HelpGetTask;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.Map;

public interface HelpGetTaskRepository extends Repository<HelpGetTask, Long> {


    @Query(value = "select p.id,p.classify_id as classifyId,p.lucky_value as luckyValue,p.title,p.title_picture as titlePicture,p.content,p.create_time as createTime," +
            "t.picture,t.content as Tcontent,t.state,t.create_time as tCreateTime " +
            "from help_get_task t " +
            "left join help_task_platform p on t.help_task_platform_id=p.id " +
            "where t.user_id=:userId",
            countQuery = "select count(*) from help_get_task t " +
                    "left join help_task_platform p on t.help_task_platform_id=p.id " +
                    "where t.user_id=:userId",
            nativeQuery = true)
    Page<Map<String,Object>> selectHelpGetTaskList(Pageable pageable, @Param("userId") Long userId);

    @Query(value = "select p.id,p.classify_id as classifyId,p.lucky_value as luckyValue,p.title,p.title_picture as titlePicture,p.content,p.create_time as createTime," +
            "t.picture,t.content as Tcontent,t.state,t.create_time as tCreateTime " +
            "from help_get_task t " +
            "left join help_task_platform p on t.help_task_platform_id=p.id " +
            "where t.user_id=:userId and t.state=:state",
            countQuery = "select count(*) from help_get_task t " +
                    "left join help_task_platform p on t.help_task_platform_id=p.id " +
                    "where t.user_id=:userId and t.state=:state",
            nativeQuery = true)
    Page<Map<String,Object>> selectHelpGetTaskList(Pageable pageable, @Param("userId") Long userId,@Param("state") Integer state);

    @Query(value = "select p.id,p.classify_id as classifyId,p.lucky_value as luckyValue,p.title,p.title_picture as titlePicture,p.content,p.create_time as createTime," +
            "t.id as Tid,t.picture,t.content as Tcontent,t.state,t.audit_content as auditContent,t.create_time as tCreateTime " +
            "from help_get_task t " +
            "left join help_task_platform p on t.help_task_platform_id=p.id " +
            "where p.id=:id and t.user_id=:userId ",
            nativeQuery = true)
    Map<String,Object> selectHelpGetTaskById(@Param("id") Long id,@Param("userId") Long userId);

    @Query(value = "select 1 from help_get_task t " +
            "left join help_task_platform p on t.help_task_platform_id=p.id " +
            "where p.id=:id and  t.user_id=:userId limit 1",nativeQuery = true)
    Integer selectCount(@Param("id") Long id,@Param("userId") Long userId);

    @Modifying(clearAutomatically = true)
    @Query(value = "update help_get_task set state=:state,audit_content=:auditContent where id=:id",nativeQuery = true)
    int updateHelpGetTaskState(@Param("id") Long id,@Param("state") Integer state,@Param("auditContent") String auditContent);


    @Query(value = "select t.id,p.classify_id as classifyId,p.title_picture as titlePicture,p.lucky_value as luckyValue,p.title,p.content,p.create_time as createTime," +
            "t.picture,t.content as Tcontent,t.state,t.create_time as tCreateTime,t.audit_content as auditContent,t.user_id as userId " +
            "from help_get_task t " +
            "left join help_task_platform p on t.help_task_platform_id=p.id " +
            "where t.state in (1,2,3,4) group by t.id order by t.create_time desc ",
            countQuery = "select count(*) from help_get_task t " +
                    "left join help_task_platform p on t.help_task_platform_id=p.id" +
                    " where t.state in (1,2,3,4) ",
            nativeQuery = true)
    Page<Map<String,Object>> selectHelpGetTaskList(Pageable pageable);

    @Query(value = "select t.id,p.classify_id as classifyId,p.title_picture as titlePicture,p.lucky_value as luckyValue,p.title,p.content,p.create_time as createTime," +
            "t.picture,t.content as Tcontent,t.state,t.create_time as tCreateTime,t.audit_content as auditContent,t.user_id as userId " +
            "from help_get_task t " +
            "left join help_task_platform p on t.help_task_platform_id=p.id " +
            "where t.state=1 group by t.id order by t.create_time desc ",
            countQuery = "select count(*) from help_get_task t " +
                    "left join help_task_platform p on t.help_task_platform_id=p.id" +
                    " where t.state=1 ",
            nativeQuery = true)
    Page<Map<String,Object>> selectUnderwayHelpGetTaskList(Pageable pageable);

    @Query(value = "select * from help_get_task where id=:id",nativeQuery = true)
    HelpGetTask selectById(@Param("id") Long id);


}